package cn.leedsoft.luma.common.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.google.common.base.Preconditions.checkNotNull;

/**
 * @author zhanglu
 * @date 2015年12月22日 上午9:21:16
 */
@SuppressWarnings({"rawtypes"})
public class ExcelUtil {
    private HttpServletResponse response;

    public ExcelUtil(HttpServletResponse response) {
        this.response = response;
    }

    enum Suffix {
        XLS(".xls"), XLSX(".xlsx");

        Suffix(String name) {
            this.name = name;
        }

        private String name;

        public String getName() {
            return this.name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public static Suffix getEnumByName(String name) {
            for (Suffix s : Suffix.values()) {
                if (s.getName().equals(name))
                    return s;
            }
            return null;
        }
    }

    public static void main(String[] args) {

    }

    /**
     * @param filepath Excel文件路径
     * @return 1、返回一个Map<String,Object>构成的list。<br>
     * 2、list.get(0)返回标题行信息。<br>
     * 3、list.get(1)返回字段映射信息。要求模板标题行的注释必须为相应字段<br>
     * 4、其他依次为各行所读取的数据。 5、Map的key为cell【columnIndex】，例如第一列为cell0
     * @desc 1、读取多个sheet页。不存在合并行列。<br>
     * 2、每页第一行为标题行。标题行，必须设置字段注释。<br>
     * 3、第一列为非空列，若为空，则判定该行无数据。本sheet页读取完毕。<br>
     * 4、主要用于批量导入数据.
     */
    public static List<Map<String, Object>> importXls(String filepath) throws Exception {
        File file = new File(filepath);
        String fileName = file.getName();
        String fileSuffix = fileName.substring(fileName.lastIndexOf("."), fileName.length());
        InputStream is = new FileInputStream(file);

        return importXls(is, Suffix.getEnumByName(fileSuffix));
    }

    /**
     * @requestFunction importXls(String filepath)方法的另一种形式。可以接受文件流
     */
    public static List<Map<String, Object>> importXls(InputStream is, Suffix fileSuffix) {
        if (is == null)
            return null;
        List<Map<String, Object>> results = null;
        Workbook workbook = null;
        try {
            if (Suffix.XLS.equals(fileSuffix)) {
                workbook = new HSSFWorkbook(is);
            } else if (Suffix.XLSX.equals(fileSuffix)) {
                workbook = new XSSFWorkbook(is);
            } else {
                is.close();
                throw new RuntimeException("导入的文件不是Excel格式");
            }
            results = readData(workbook);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null)
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
        return results;
    }

    /**
     * @desc 分sheet页，读取一组关联性数据。sheet页有前后顺序。在前的先导入<br>
     * 每个sheet页构成一个List，依次放入一个总List中返回。每个sheet页读取规则同importXls
     */
    public static List<List<Map<String, Object>>> importXlsBySheets(String filepath) throws Exception {
        File file = new File(filepath);
        String fileName = file.getName();
        String fileSuffix = fileName.substring(fileName.lastIndexOf("."), fileName.length());
        InputStream is = new FileInputStream(file);
        return importXlsBySheets(is, Suffix.getEnumByName(fileSuffix));
    }

    public static List<List<Map<String, Object>>> importXlsBySheets(InputStream is, Suffix fileSuffix) {
        List<List<Map<String, Object>>> results = new ArrayList<List<Map<String, Object>>>();
        Workbook workbook = null;
        try {
            if (Suffix.XLS.equals(fileSuffix)) {
                workbook = new HSSFWorkbook(is);
            } else if (Suffix.XLSX.equals(fileSuffix)) {
                workbook = new XSSFWorkbook(is);
            } else {
                is.close();
                throw new RuntimeException("导入的文件不是Excel格式");
            }
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workbook.getSheetAt(numSheet);
                results.add(readData(sheet, true));
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null)
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
        return results;
    }

    /**
     * TODO data超过多少条数据之后，分sheet页。目前excel2003，最多一页65536条
     *
     * @return 生成一个Excel，并直接提供下载。
     * @requestFunction 提供一个查询出来的数据集合data，
     * 一个指定Map中的field和导出的Excel中字段对应关系的headers， 一个Excel文件名title，
     * 将对应的列表，导出成不存在合并行列、不分sheet页的Excel。<br>
     * headers，格式如：{{"name", "姓名"},{"phone", "电话"},...}<br>
     * headers，决定相应字段的排列顺序。
     */
    public <T> void exportXls(List<T> data, String[][] headers, String title) {
        if (StringUtils.isBlank(title)) title = "success";
        else {
            try {
                title = new String(title.getBytes("gb2312"), "iso8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
        // 提供下载
        response.setContentType("octets/stream");
        response.addHeader("Content-Disposition", "attachment;filename=" + title + ".xls");
        OutputStream os = null;
        try {
            os = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        dataToXls(data, headers, os);
    }

    public static <T> void generateXls(List<T> data, String[][] headers, String title) {
        if (StringUtils.isBlank(title)) title = "success";
        File f = new File("C:/leedsjung_export/" + title + ".xls");
        if (!f.getParentFile().exists()) f.getParentFile().mkdirs();
        OutputStream os = null;
        try {
            os = new FileOutputStream(f);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
        dataToXls(data, headers, os);
    }

    private static <T> void dataToXls(List<T> data, String[][] headers, OutputStream os) {
        checkNotNull(data);
        checkNotNull(headers);
        checkNotNull(os);
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet("sheet0");
        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(25);
        // 生成标题行样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleStyle.setFont(font);
        // 生成普通行样式
        HSSFCellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        normalStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        normalStyle.setFont(font2);

        // 产生表格标题行
        int index = 0;
        HSSFRow row = sheet.createRow(index);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(titleStyle);
            HSSFRichTextString text = new HSSFRichTextString(headers[i][1]);
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        Iterator<T> it = data.iterator();
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T dataOne = it.next();
            for (int i = 0; i < headers.length; i++) {
                String header = headers[i][0];
                if (StringUtils.isBlank(header)) {
                    throw new RuntimeException("服务器错误：调用excel生成工具时参数headers传递错误");
                }
                Object cellValueObj = null;
                if (dataOne instanceof Map) {
                    cellValueObj = ((Map) dataOne).get(header);
                } else {
                    Method method = null;
                    try {
                        method = dataOne.getClass().getMethod("get" + Character.toUpperCase(header.charAt(0)) + header.substring(1));
                    } catch (NoSuchMethodException | SecurityException e) {
                        e.printStackTrace();
                    }
                    if (method != null) {
                        try {
                            cellValueObj = method.invoke(dataOne);
                        } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                }
                String cellValue = "";
                if (cellValueObj != null && StringUtils.isNotBlank(cellValueObj.toString())) {
                    if (cellValueObj instanceof Date) {
                        cellValue = df.format((Date) cellValueObj);
                    } else {
                        cellValue = cellValueObj.toString();
                    }
                }

                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(normalStyle);
                HSSFRichTextString text = new HSSFRichTextString(cellValue);
                cell.setCellValue(text);
            }
        }

        try {
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static int getColCount(Sheet sheet) {
        int colCount = 0;
        if (getRowCount(sheet) == 0) {// 0行
            return colCount;
        } else {
            Row row = sheet.getRow(0);
            for (Iterator<Cell> it = row.iterator(); it.hasNext(); ) {
                Cell cell = it.next();
                if (isCellNull(cell))
                    return colCount;
                else
                    colCount++;
            }
        }
        return colCount;
    }

    /**
     * @requestFunction 提供一个sheet对象，返回该sheet页中有效行数<br>
     * 计算法则：若某行第一列Cell为空（null或trim后是""），则判定该行为结束行。计算行数
     */
    private static int getRowCount(Sheet sheet) {
        int rowCount = 0;
        for (Iterator<Row> it = sheet.iterator(); it.hasNext(); ) {
            Row row = it.next();
            Cell cell = row.getCell(0);
            if (isCellNull(cell)) {
                break;
            } else
                rowCount++;
        }
        return rowCount;
    }

    /**
     * @requestFunction 提供一个Cell对象，当Cell为null或者Cell原始内容trim之后是""，返回true。即该Cell为空
     */
    private static boolean isCellNull(Cell cell) {
        if (cell == null || (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().trim().equals("")))
            return true;
        else
            return false;
    }

    /**
     * @desc 读取一个workbook对象中的数据
     */
    private static List<Map<String, Object>> readData(Workbook workbook) throws IOException {
        List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (numSheet == 0) {// 不读取非第一页的标题行
                datas.addAll(readData(sheet, true));
            } else {
                datas.addAll(readData(sheet, false));
            }
        }
        return datas;
    }

    /**
     * @param flag 是否读取第一行（标题行）。true，为读取。
     * @desc 读取一个sheet页的数据。
     */
    private static List<Map<String, Object>> readData(Sheet sheet, boolean flag) throws IOException {
        List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
        int colCount = getColCount(sheet);// 模板列数，以标题行为准。

        int rowCount = getRowCount(sheet);// 模板每页的行数
        // 循环行Row
        for (int numRow = 0; numRow < rowCount; numRow++) {
            Row row = sheet.getRow(numRow);// 当row中所有cell都为null时，该row为null
            Map<String, Object> rowData = new HashMap<String, Object>();
            // 循环列
            for (int colNum = 0; colNum < colCount; colNum++) {
                Cell cell = row.getCell(colNum);
                rowData.put("cell" + colNum, getValue(cell));
            }
            if (flag) {
                datas.add(rowData);
                // 解析标题行注释，生成字段映射，加入list.get(1)
                if (numRow == 0) {
                    Map<String, Object> filedsMap = new HashMap<String, Object>();
                    // 循环列
                    for (int colNum = 0; colNum < colCount; colNum++) {
                        Cell cell = row.getCell(colNum);
                        filedsMap.put("cell" + colNum, cell.getCellComment().getString().getString().trim());
                    }
                    datas.add(filedsMap);
                }
            } else if (numRow != 0) {
                datas.add(rowData);
            }
        }
        return datas;
    }

    /**
     * @requestFunction 提供一个Cell对象，拿到其中的内容。<br>
     * 如果该Cell为空（null或trim后为""），则返回null
     */
    private static String getValue(Cell cell) {
        if (isCellNull(cell))
            return null;
        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            Date d = cell.getDateCellValue();
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            return df.format(d);
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return cell.getStringCellValue().trim();
        }
    }
}
